package com.fjnu.dao.impl;

import com.fjnu.dao.ArticleDao;
import com.fjnu.dto.ArticleConditionDto;
import com.fjnu.entity.Article;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;



@Repository
public class  ArticleDaoImpl implements ArticleDao {

    @Autowired
    JdbcTemplate jdbcTemplate ;

    @Override
    public int insert(Article article) {
        // 检查必填字段是否为空
        String sql = "INSERT INTO tb_article (user_id,title, content, category_id, create_time, update_time) " +
                "VALUES ( ?,?, ?, ?, NOW(), NOW())";
        int result = jdbcTemplate.update(sql,
                article.getUserId(),
                article.getTitle(),
                article.getContent(),
                article.getCategoryId());
        return result > 0 ? 1 : 0;

    }

    @Override
    public int updateContent(Integer id, String newContent) {
        if (id == null) {
            return 0; // 插入失败
        }
        String sql = "UPDATE tb_article SET content = ? WHERE id = ?";
        int result = jdbcTemplate.update(sql, newContent, id);
        return result > 0 ? 1 : 0;
    }

    @Override
    public int updateTitle(Integer id, String newTitle) {
        if (id == null) {
            return 0; // 插入失败
        }
        String sql = "UPDATE tb_article SET title = ? WHERE id = ?";
        int result = jdbcTemplate.update(sql, newTitle, id);
        return result > 0 ? 1 : 0;
    }

    @Override
    public int updateByCondition(Integer id,ArticleConditionDto articleCondition) {
        StringBuilder sqlBuilder = new StringBuilder("UPDATE tb_article SET ");
        if (articleCondition.getTitle() != null) {
            sqlBuilder.append("title ='").append(articleCondition.getTitle()).append("', ");
        }
        if (articleCondition.getContent() != null) {
            sqlBuilder.append("content ='").append(articleCondition.getContent()).append("', ");
        }
        if (articleCondition.getCategoryId() != null) {
            sqlBuilder.append("category_id ='").append(articleCondition.getCategoryId()).append("', ");
        }
        if (articleCondition.getDescription() != null) {
            sqlBuilder.append("description ='").append(articleCondition.getDescription()).append("', ");
        }
        if (articleCondition.getStatus() != null) {
            sqlBuilder.append("status ='").append(articleCondition.getStatus()).append("'");
        }
        sqlBuilder.append(" WHERE id = ").append(id);
        return jdbcTemplate.update(sqlBuilder.toString());

    }

    @Override
    public int updateStatus(Integer id, Integer newStatus) {
        if (id == null) {
            return 0; // 插入失败
        }
        String sql = "UPDATE tb_article SET status = ? WHERE id = ?";
        int result = jdbcTemplate.update(sql, newStatus, id);
        return result > 0 ? 1 : 0;
    }

    @Override
    public int batchUpdateStatus(List<Integer> ids, Integer newStatus) {
        if (ids == null || ids.isEmpty()) {
            return 0; // 插入失败
        }
        int i = 0;
        for (Integer id : ids) {
            String sql = "UPDATE tb_article SET status = ? WHERE id = ?";
            int result = jdbcTemplate.update(sql, newStatus, id);
            if (result > 0) {
                i++;
            }
        }
        return i;
    }

    @Override
    public int deleteById(Integer id) {
        if (id == null) {
            return 0; // 插入失败
        }
        String sql = "DELETE FROM tb_article WHERE id = ?";
        int result = jdbcTemplate.update(sql, id);
        return result > 0 ? 1 : 0;
    }

    @Override
    public List<Article> selectByUserId(Integer userId) {
        if (userId == null) {
            return null; // 查询失败
        }
        String sql = "SELECT * FROM tb_article ta WHERE  ta.user_id = ? order by ta.create_time desc " ;
        return jdbcTemplate.query(sql, new ArticleRowMapper(), userId);

    }

    @Override
    public List<Article> selectByKeyWord(String keyword) {

        String sql = "SELECT * FROM tb_article  ta WHERE " +
                " ta.title LIKE ? OR ta.content LIKE ? order by ta.create_time desc" ;
        String likeKeyWord = "%"+keyword+"%";
        return jdbcTemplate.query(sql, new ArticleRowMapper(),  likeKeyWord, likeKeyWord);

    }


    @Override
    public List<Article> selectByTimePeriod(Date startTime, Date endTime) {
        if (startTime == null || endTime == null) {
            return null; // 查询失败
        }
        String sql = "SELECT * FROM tb_article ta WHERE " +
                " ta.create_time >= ? AND ta.create_time <= ? order by ta.create_time desc" ;
        return jdbcTemplate.query(sql, new ArticleRowMapper(), startTime, endTime);
    }

    @Override
    public List<Article> selectByTag(Integer tagId) {
        if (tagId == null)
            return null;
        String sql="SELECT *\n" +
                "FROM tb_article ta  JOIN tb_article_tag tt\n" +
                "ON ta.id=tt.article_id\n" +
                "WHERE tt.tag_id=? ORDER BY create_time desc ";
        return jdbcTemplate.query(sql, new ArticleRowMapper(), tagId);
    }

    @Override
    public List<Article> selectByCategory(Integer categoryId) {
        if (categoryId == null)
            return null;
        String sql = "SELECT * FROM tb_article ta WHERE " +
                " ta.category_id = ? order by ta.create_time desc" ;
        return jdbcTemplate.query(sql, new ArticleRowMapper(), categoryId);
    }

    @Override
    public List<Article> selectByConditionWithLimit(ArticleConditionDto articleConditionDto, Integer startIndex, Integer endIndex) {
        StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM tb_article WHERE 1=1");
        if (articleConditionDto.getUserId() != null) {
            sqlBuilder.append(" AND user_id = ").append(articleConditionDto.getUserId());
        }
        if (articleConditionDto.getTitle() != null) {
            sqlBuilder.append(" AND title = '").append(articleConditionDto.getTitle()).append("'");
        }
        if (articleConditionDto.getContent() != null) {
            sqlBuilder.append(" AND content = '").append(articleConditionDto.getContent()).append("'");
        }
        if (articleConditionDto.getStatus() != null) {
            sqlBuilder.append(" AND status = ").append(articleConditionDto.getStatus());
        }
        if (articleConditionDto.getDescription() != null) {
            sqlBuilder.append(" AND description = '").append(articleConditionDto.getDescription()).append("'");
        }
        if (articleConditionDto.getCategoryId() != null) {
            sqlBuilder.append(" AND category_id = ").append(articleConditionDto.getCategoryId());
        }
        sqlBuilder.append(" LIMIT ").append(startIndex).append(",").append(endIndex);
        return jdbcTemplate.query(sqlBuilder.toString(), new ArticleRowMapper());


    }

    @Override
    public List<Article> list(ArticleConditionDto articleConditionDto) {
        StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM tb_article WHERE 1=1");
        if (articleConditionDto.getUserId() != null&& !(articleConditionDto.getUserId().equals( ""))) {
            sqlBuilder.append(" AND user_id = ").append(articleConditionDto.getUserId());
        }
        if (articleConditionDto.getTitle() != null&& !(articleConditionDto.getTitle().equals(""))) {
            sqlBuilder.append(" AND title = '").append(articleConditionDto.getTitle()).append("'");
        }
        if (articleConditionDto.getContent() != null&& !(articleConditionDto.getContent().equals(""))) {
            sqlBuilder.append(" AND content = '").append(articleConditionDto.getContent()).append("'");
        }
        if (articleConditionDto.getStatus() != null&& !(articleConditionDto.getStatus().equals(""))) {
            sqlBuilder.append(" AND status = ").append(articleConditionDto.getStatus());
        }
        if (articleConditionDto.getDescription() != null&& !(articleConditionDto.getDescription().equals(""))) {
            sqlBuilder.append(" AND description = '").append(articleConditionDto.getDescription()).append("'");
        }
        if (articleConditionDto.getCategoryId() != null&& !(articleConditionDto.getCategoryId().equals(""))) {
            sqlBuilder.append(" AND category_id = ").append(articleConditionDto.getCategoryId());
        }
        return jdbcTemplate.query(sqlBuilder.toString(), new ArticleRowMapper());
    }

    /**
     *  RowMapper接口的实现类，用于将查询结果映射为Article对象
     */
    private static class ArticleRowMapper implements RowMapper<Article> {


        @Override
        public Article mapRow(ResultSet rs, int rowNum) throws SQLException {
            Article article = new Article();
            article.setId(rs.getInt("id"));
            article.setTitle(rs.getString("title"));
            article.setContent(rs.getString("content"));
            article.setUserId(rs.getInt("user_id"));
            article.setCategoryId(rs.getInt("category_id"));
            article.setCreateTime(rs.getTimestamp("create_time"));
            article.setUpdateTime(rs.getTimestamp("update_time"));
            return article;
        }
    }
}
